package com.xteam.ecsystem.dao.Impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import PluSoft.Utils.Convert;

import com.xteam.ecsystem.dao.IFrontGoodsDao;
import com.xteam.ecsystem.entities.BaseGoodsAttribute;
import com.xteam.ecsystem.entities.BaseGoodsBrand;
import com.xteam.ecsystem.entities.BaseGoodsGroup;
import com.xteam.ecsystem.entities.BaseGoodsInfo;
import com.xteam.ecsystem.entities.BaseGoodsPicture;
import com.xteam.ecsystem.entities.BaseGroupAttribute;
import com.xteam.ecsystem.entities.BaseRefGoods;
import com.xteam.ecsystem.entities.BaseTypeAttribute;
import com.xteam.ecsystem.utils.FrontGoodsCenter;
import com.xteam.ecsystem.utils.PageCenterStation;
@Repository
@SuppressWarnings("unchecked")
/**
 * 前端商品信息相关数据获取实现类
 */
public class FrontGoodsDaoImpl extends DDLPrefix implements IFrontGoodsDao {
	
	@Autowired
	private BaseDaoImpl baseDao;
	
	/**
	 * 根据商品类型，商品分类，页码，页大小获取商品列表
	 */
	public PageCenterStation<?> getGoodsList(String goodstype, String goodsgroup, String brand,String attr, int pricemin, int pricemax, int pageIndex,
			int pageSize,String keywords) {
		try{
			List list = new ArrayList();
			String sql = "";
			String countSQL = "";
			String innerSql = " 1=1 ";
			if(goodstype.equals(FrontGoodsCenter.TYPE_BEST)){
				innerSql = " bgi.bgi_best=1 ";
			}else if(goodstype.equals(FrontGoodsCenter.TYPE_GROUP)){
				innerSql = " bgi.bgi_buy=1 ";
			}else if(goodstype.equals(FrontGoodsCenter.TYPE_HOT)){
				innerSql = " bgi.bgi_hot=1 ";
			}else if(goodstype.equals(FrontGoodsCenter.TYPE_NEW)){
				innerSql = " bgi.bgi_new=1 ";
			}
			sql = "select bgi.bgi_id as bgiId,bgi.bgi_name as bgiName,bgi.bgi_gsn as bgiGsn,bgi.bgi_group as bgiGroup,bgi.bgi_group as bgiGroup,bgi.bgi_brand as bgiBrand,"
				+ "bgi.bgi_supplier as bgiSupplier,bgi.bgi_price as bgiPrice,bgi.bgi_market as bgiMarket,bgi.bgi_feature as bgiFeature,bgi.bgi_detail as bgiDetail,"
				+ "bgi.bgi_standard as bgiStandard,bgi.bgi_weight as bgiWeight,bgi.bgi_number as bgiNumber,bgi.bgi_warn as bgiWarn,bgi.bgi_best as bgiBest,"
				+ "bgi.bgi_new as bgiNew,bgi.bgi_hot as bgiHot,bgi.bgi_buy as bgiBuy,bgi.bgi_on as bgiOn,bgi.bgi_off as bgiOff,bgi.bgi_order as bgiOrder," 
				+ "bgi.bgi_otime as bgiOtime,bgi.bgi_keyword as bgiKeyword,bgi.bgi_state as bgiState from base_goods_info as bgi " + SQL_WHERE + innerSql;
			countSQL = "select count(*) from base_goods_info as bgi " + SQL_WHERE + innerSql;
			if(attr!=null){
				if(!attr.equals("")){
					String str = "";
					int count = 0;
					//生成in条件中的值，及值的个数
					String attribute[] = attr.split(",");
					for (String id : attribute) {
						if (!"0".equals(id)) {
							str += "'"+id+"',";
							count++;
						}
					}
					//如果最终生成的查询str里面有属性，去除最后一个逗号
					if(str.length()>0){
						str = str.substring(0, str.length()-1);
						sql = "select bgi.bgi_id as bgiId,bgi.bgi_name as bgiName,bgi.bgi_gsn as bgiGsn,bgi.bgi_group as bgiGroup,bgi.bgi_group as bgiGroup,bgi.bgi_brand as bgiBrand,"
							+ "bgi.bgi_supplier as bgiSupplier,bgi.bgi_price as bgiPrice,bgi.bgi_market as bgiMarket,bgi.bgi_feature as bgiFeature,bgi.bgi_detail as bgiDetail,"
							+ "bgi.bgi_standard as bgiStandard,bgi.bgi_weight as bgiWeight,bgi.bgi_number as bgiNumber,bgi.bgi_warn as bgiWarn,bgi.bgi_best as bgiBest,"
							+ "bgi.bgi_new as bgiNew,bgi.bgi_hot as bgiHot,bgi.bgi_buy as bgiBuy,bgi.bgi_on as bgiOn,bgi.bgi_off as bgiOff,bgi.bgi_order as bgiOrder," 
							+ "bgi.bgi_otime as bgiOtime,bgi.bgi_keyword as bgiKeyword,bgi.bgi_state as bgiState from base_goods_info as bgi "
							+ "where bgi.bgi_id" 
							+ " in("
							+ "	 	select bga.bga_goods FROM base_goods_attribute bga,"
							+ "     ("
							+ "     	select bga_tattri, bga_value FROM base_goods_attribute WHERE bga_id IN ("+str+") "
							+ "     ) as tab"
							+ "     WHERE bga.bga_tattri = tab.bga_tattri AND bga.bga_value = tab.bga_value "
							+ "     GROUP BY bga.bga_goods HAVING COUNT(bga.bga_goods) = ?"
							+ "   )";
						countSQL = "select count(*)from base_goods_info as bgi "
							+ "where bgi.bgi_id" 
							+ " in("
							+ "	 	select bga.bga_goods FROM base_goods_attribute bga,"
							+ "     ("
							+ "     	select bga_tattri, bga_value FROM base_goods_attribute WHERE bga_id IN ("+str+") "
							+ "     ) as tab"
							+ "     WHERE bga.bga_tattri = tab.bga_tattri AND bga.bga_value = tab.bga_value "
							+ "     GROUP BY bga.bga_goods HAVING COUNT(bga.bga_goods) = ?"
							+ "   )";
						list.add(count);
					}
				}
			}
			if(goodsgroup!=null){
				if(!goodsgroup.equals("") && !goodsgroup.equals("0")){
					sql += " and bgi.bgi_group = ? ";
					countSQL += " and bgi.bgi_group = ? ";
					list.add(goodsgroup);
				}
			}
			if(brand!=null){
				if(!brand.equals("") && !brand.equals("0")){
					sql += " and bgi.bgi_brand = ? ";
					countSQL += " and bgi.bgi_brand = ? ";
					list.add(brand);
				}
			}
			if(pricemax>0){
				sql += " and bgi.bgi_price between ? and ?";
				countSQL += " and bgi.bgi_price between ? and ?";
				list.add(Convert.toDouble(pricemin));
				list.add(Convert.toDouble(pricemax));
			}
			sql += " and bgi.bgi_name like '%"+keywords+"%'";
			countSQL += " and bgi.bgi_name like '%"+keywords+"%'";
			sql += " order by bgi.bgi_order ";
			countSQL += " order by bgi.bgi_order ";
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao.
			queryWithSQL(BaseGoodsInfo.class, countSQL, sql, null, list.toArray(), (pageIndex - 1)* pageSize, pageSize);
			return p;
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 
	 * 根据商品ID获取商品信息
	 * @see com.xteam.ecsystem.dao.IFrontGoodsDao#getGoodsInfoById(java.lang.String)
	 */
	@Override
	public BaseGoodsInfo getGoodsInfoById(String id) {
		BaseGoodsInfo bgi = new BaseGoodsInfo();
		try {
			String hql = SQL_SELECT + " new " + BaseGoodsInfo.REF + " (bgi.bgiId, bgi.bgiName, bgi.bgiGsn, bgi.bgiGroup,bgi.bgiBrand, bgi.bgiSupplier,"
					+ " bgi.bgiPrice, bgi.bgiMarket,bgi.bgiFeature, bgi.bgiDetail, bgi.bgiStandard, bgi.bgiWeight,bgi.bgiNumber, bgi.bgiWarn,bgi.bgiBest," 
					+ " bgi.bgiNew, bgi.bgiHot, bgi.bgiBuy, bgi.bgiOn, bgi.bgiOff, bgi.bgiOrder, bgi.bgiOtime, bgi.bgiKeyword, bgi.bgiState, brg.brgIsDouble, bgg.bggName,bgb.bgbName)"
					+ SQL_FROM + BaseGoodsInfo.REF + " as bgi, "
					+ BaseRefGoods.REF + " as brg," + BaseGoodsGroup.REF + " as bgg,"
					+ BaseGoodsBrand.REF + " as bgb "
					+ SQL_WHERE + " bgi.bgiId=? ";
			bgi = (BaseGoodsInfo) this.baseDao.findObject(hql, new Object[] { id });
		} catch (Exception e) {
			e.printStackTrace();
		}
		return bgi;
	}
	/**
	 * 
	 * 根据商品ID，是否为价格属性获取属性信息。
	 * @see com.xteam.ecsystem.dao.IFrontGoodsDao#getGoodsAttrById(java.lang.String, boolean)
	 */
	@Override
	public List<?> getGoodsAttrById(String goodsId) {
		if (goodsId != null) {
			List list = new ArrayList();
			String hql = SQL_SELECT + " new " + BaseGoodsAttribute.REF
					+ " (bga.bgaId,bga.bgaValue,bga.bgaPrice,bga.bgaTattri,bta.btaName,bta.btaAtype,bta.btaState)" 
					+ SQL_FROM + BaseGoodsAttribute.REF + " as bga, "
					+ BaseTypeAttribute.REF + " as bta "+ SQL_WHERE
					+ " bga.bgaTattri = bta.btaId " + SQL_AND
					+ " bga.bgaGoods = '"+goodsId+"' "
					+ " order by bta.btaOrder,bga.bgaTattri,bga.bgaPrice";
			list = this.baseDao.find(hql);
			return list;
		} else {
			return null;
		}
	}
	@Override
	public PageCenterStation<?> getRelatedGoods(String goodsId, int pageIndex,
			int pageSize) {
		List list = new ArrayList();
		if (goodsId != null) {
			String hql = SQL_SELECT + " new " + BaseGoodsInfo.REF
					+ " (bgi.bgiId, bgi.bgiName, brf.brgIsDouble, bgi.bgiPrice)" + SQL_FROM
					+ BaseRefGoods.REF + " as brf, " + BaseGoodsInfo.REF
					+ " as bgi " + SQL_WHERE + " brf.brgRgoods = bgi.bgiId "
					+ SQL_AND + " brf.brgMgoods = ? ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ BaseRefGoods.REF + " as brf, " + BaseGoodsInfo.REF
					+ " as bgi " + SQL_WHERE + " brf.brgRgoods = bgi.bgiId "
					+ SQL_AND + " brf.brgMgoods = ? ";
			list.add(goodsId);
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);
			return p;
		}else {
			return null; 
		}
	}
	@Override
	public List<?> getFilterAttr(String group) {
		if(group!=null){
			List list = new ArrayList();
			String hql = SQL_SELECT + " new " + BaseGoodsAttribute.REF
					+ " (bga.bgaId, bga.bgaGoods, bga.bgaGtype, bga.bgaTattri, bga.bgaValue, bga.bgaPrice,"
					+" bga.bgaOrder, bta.btaName, bta.btaAtype, bta.btaState) "
					+ SQL_FROM + BaseGroupAttribute.REF + " as bgr, " + BaseGoodsAttribute.REF + " as bga, "
					+ BaseTypeAttribute.REF + " as bta "
					+ SQL_WHERE + " bgr.bgaGgroup = '"+ group +"' and bgr.bgaTattri = bga.bgaTattri " 
					+ " and bta.btaId = bga.bgaTattri and bta.btaState = 1"
					+ "group by bga.bgaValue order by bga.bgaTattri,bga.bgaValue";
			list = this.baseDao.find(hql);
			return list;
		}else {
			return null;
		}
	}
	@Override
	public List<?> getFilterBrand(String group) {
		if(group!=null){
			List list = new ArrayList();
			String hql = SQL_SELECT + " new " + BaseGoodsBrand.REF
					+ " (bgb.bgbId, bgb.bgbName, bgb.bgbUrl,bgb.bgbLogo, bgb.bgbDesc, bgb.bgbOrder, bgb.bgbState) "
					+ SQL_FROM + BaseGoodsBrand.REF + " as bgb, " + BaseGoodsInfo.REF + " as bgi "
					+ SQL_WHERE + " bgi.bgiGroup = '"+ group +"' and bgb.bgbId = bgi.bgiBrand " 
					+ " and bgb.bgbState = 1 and bgi.bgiState = 1"
					+ "group by bgb.bgbName order by bgb.bgbOrder";
			list = this.baseDao.find(hql);
			if(list.isEmpty()){
				list = null;
			}
			return list;
		}else {
			return null;
		}
	}
	@Override
	public List<?> getFilterPrice(String group) {
		if(group!=null){
			List list = new ArrayList();
			String hql = SQL_SELECT + " new " + BaseGoodsGroup.REF
					+ " (bgg.bggSection,MIN(bgi.bgiPrice),MAX(bgi.bgiPrice)) "
					+ SQL_FROM + BaseGoodsGroup.REF + " as bgg, " + BaseGoodsInfo.REF + " as bgi "
					+ SQL_WHERE + " bgg.bggId = '"+ group +"' and bgi.bgiGroup = bgg.bggId " 
					+ " and bgg.bggState = 1 and bgi.bgiState = 1";
			list = this.baseDao.find(hql);
			if(list.isEmpty()){
				list = null;
			}
			return list;
		}else {
			return null;
		}
	}
	/**
	 * 
	 * 获取商品第一张相片
	 * @see com.xteam.ecsystem.dao.IFrontGoodsDao#getGoodsPic(java.lang.String)
	 */
	@Override
	public BaseGoodsPicture getGoodsPic(String goodsId) {
		BaseGoodsPicture bgp = new BaseGoodsPicture();
		String hql = SQL_FROM + BaseGoodsPicture.REF + SQL_WHERE + " bgpGoods = ? and bgpState = 1 order by bgpOrder";
		List list = this.baseDao.find(hql, new Object[]{ goodsId });
		if(!list.isEmpty()){
			bgp = (BaseGoodsPicture) list.get(0);
			return bgp;
		}else {
			return null;
		}
	}
	
	/**
	 * 
	 * 获取所有相片
	 * @see com.xteam.ecsystem.dao.IFrontGoodsDao#getGoodsPicList(java.lang.String)
	 */
	@Override
	public List<?> getGoodsPicList(String goodsId) {
		try {
			String hql = SQL_FROM + BaseGoodsPicture.REF + SQL_WHERE + " bgpGoods = ? and bgpState = 1 order by bgpOrder";
			List list = this.baseDao.find(hql, new Object[]{ goodsId });
			return list;
		} catch (Exception e) {
			return null;
		}
	}
}
